package com.njcb.ams.store.text.transform;

import java.io.BufferedWriter;
import java.io.File;
import java.io.FileWriter;
import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.Date;
import java.util.Hashtable;
import java.util.List;

import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

/**
 * 数据库导出到文本
 * 
 * @author 刘彦龙
 */
public class TableToText {
	private static final Logger logger = LoggerFactory.getLogger(TableToText.class);
	// 要导出的表名称
	private String tableName = "";
	// 生成文件全名
	private String fileName = "";
	// 文件字段分割字符
	private String sepertor = "|";
	// 用户自己组装SQL
	private String userSelectSql = "";
	// 查询条件
	private String whereStr = "";
	// 是否生成.OK文件
	private boolean isOKfile = false;
	// 是否追加文件
	private boolean isAppend = false;
	private StringBuffer selectSql = new StringBuffer();
	private List<ColumnInfo> tableFieldList = new ArrayList<ColumnInfo>();
	private List<String> fileFieldList = new ArrayList<String>();
	private Hashtable<String, String> staticData = new Hashtable<String, String>();
	private Connection conn = null;
	private ExpDataBase dataCheck = null;
	private String charsetName = "GBK";// 文件编码格式

	public TableToText() {
	}

	public TableToText(Connection conn, String fieldNames, String tableName, String fileName) {
		this.conn = conn;
		this.tableName = tableName.toUpperCase();
		this.fileName = fileName;
	}

	public int[] doExportProcess() throws Exception {
		long startTime = System.currentTimeMillis();
		long useTime = 0L;
		if (null == dataCheck) {
			throw new Exception("请设置 addDataCheck");
		}
		initSelectSql();

		ResultSet rs = null;
		Statement state = null;
		BufferedWriter writer = null;
		int lineCount = 0;
		int validCount = 0;
		try {
			state = conn.createStatement();
			rs = state.executeQuery(selectSql.toString());

			File tempFile = new File(fileName);
			if (isAppend && tempFile.exists()) {
				writer = new BufferedWriter(new FileWriter(tempFile, true));
			} else {
				UtilsCommon.writeStringToFile(tempFile, "", charsetName);
				writer = new BufferedWriter(new FileWriter(tempFile, true));
			}
			boolean firstLine = true;

			while (rs.next()) {
				int checkCode = UtilsCommon.CHECK_RETURN_CODE_SUCCESS;
				StringBuffer buffer = new StringBuffer(1000);
				for (int i = 0; i < fileFieldList.size(); i++) {
					String filidName = fileFieldList.get(i);
					String filidValue = null;

					if (filidName.matches("<.*>")) {
						filidName = filidName.substring(1, filidName.length() - 1);
						filidValue = filidName;
					} else {
						Object columnValue = rs.getObject(filidName);
						filidValue = null == columnValue ? "" : columnValue.toString();
					}

					staticData.put(filidName.toUpperCase(), filidValue);
					checkCode = dataCheck.dataCheckAndConvert(staticData);
					buffer.append(staticData.get(filidName.toUpperCase())).append(sepertor);
				}

				if (checkCode != UtilsCommon.CHECK_RETURN_CODE_SUCCESS) {
					staticData.clear();
					continue;
				}

				buffer.delete(buffer.length() - sepertor.length(), buffer.length());

				// 第一行不换行 如果是追加且文件不空则第一行也换行
				if (!firstLine || (isAppend && tempFile.length() > 0)) {
					writer.newLine();
				}
				firstLine = false;
				writer.write(buffer.toString());
				staticData.clear();
				lineCount++;
				if (lineCount % 10000 == 0) {
					logger.debug("已处理行数 {} @时间 {}", lineCount, new Date());
				}
			}

			writer.flush();
			// 配合文件传送平台、生成.OK文件

			if (isOKfile) {
				String OKpath = fileName.concat(".OK");
				File OKfile = new File(OKpath);
				OKfile.createNewFile();
			}
		} catch (Exception e) {
			logger.error(e.getMessage(),e);
		} finally {
			if (null != rs) {
				rs.close();
			}
			if (null != state) {
				state.close();
			}
			if (null != writer) {
				writer.close();
			}
		}
		useTime = System.currentTimeMillis() - startTime;
		logger.debug("耗时: {} 分 {} 秒 {} 毫秒", useTime / 60000, useTime / 1000, useTime % 1000);
		return new int[] { lineCount, validCount };
	}

	/**
	 * 组装SQL
	 * 
	 * @throws Exception
	 */
	private void initSelectSql() throws Exception {

		// userSelectSql 有值则使用userSelectSql
		if (userSelectSql.trim().length() > 0) {
			selectSql.append(userSelectSql);
			return;
		}

		DatabaseMetaData dbMetaData = conn.getMetaData();
		ResultSet rsColumns = null;
		boolean nullField = false;
		try {
			if (fileFieldList.size() == 0) {
				nullField = true;
			}
			selectSql.setLength(0);
			rsColumns = dbMetaData.getColumns(null, null, tableName.toUpperCase(), null);
			if (rsColumns == null) {
				throw new Exception("表名[" + tableName + "]不存在");
			}
			selectSql.append("SELECT ");
			StringBuffer columnNames = new StringBuffer();
			int columnCount = 0;
			while (rsColumns.next()) {
				String columnName = rsColumns.getString("COLUMN_NAME").trim().toUpperCase();
				if (!fileFieldList.contains(columnName) && !nullField) {
					continue;
				}
				if (columnCount > 0) {
					columnNames.append(",");
				}
				columnCount++;
				columnNames.append(columnName);
				String typeName = rsColumns.getString("TYPE_NAME");
				int dataType = rsColumns.getInt("DATA_TYPE");
				ColumnInfo columnInfo = new ColumnInfo(columnName, typeName, dataType);
				tableFieldList.add(columnInfo);
			}
			selectSql.append(columnNames + " FROM " + tableName);
			selectSql.append(" " + whereStr);
			columnNames.setLength(0);
			columnNames = null;
		} finally {
			if (null != rsColumns) {
				rsColumns.close();
			}
		}
		if (fileFieldList.size() > tableFieldList.size()) {
			List<String> tableFieldNameList = new ArrayList<String>();
			for (int j = 0; j < tableFieldList.size(); j++) {
				ColumnInfo columnInfo = tableFieldList.get(j);
				tableFieldNameList.add(columnInfo.getColumnName());
			}
			for (int i = 0; i < fileFieldList.size(); i++) {
				String fieldName = fileFieldList.get(i);
				if (!tableFieldNameList.contains(fieldName) && !fieldName.matches("<.*>")) {
					throw new Exception("tableFieldList中字段: " + fieldName + " 在表中不存在");
				}
			}
		}

		if (selectSql.toString().trim().length() == 0) {
			throw new Exception("请检查参数 tableName,fileFields 或 userSelectSql");
		}

		if (fileFieldList.size() == 0) {
			String tempSql = selectSql.toString().toUpperCase();
			tempSql = tempSql.substring(tempSql.indexOf("SELECT") + 6, tempSql.indexOf("FROM")).trim();
			setFileFields(tempSql);
		}

		logger.debug(selectSql.toString());
	}

	public void addFileField(String fieldName) {
		fileFieldList.add(fieldName.toUpperCase());
	}

	public void setFileFields(String fieldNames) {
		if (fieldNames.trim().length() == 0) {
			return;
		}
		String[] fields = fieldNames.trim().split(",");
		for (int i = 0; i < fields.length; i++) {
			String fieldName = fields[i];
			if (fieldName.matches("<.*>")) {
				fileFieldList.add(fieldName);
			} else {
				fileFieldList.add(fieldName.toUpperCase());
			}
		}
	}

	public void setUserSelectSql(String userSelectSql) {
		this.userSelectSql = userSelectSql;
	}

	public void addDataCheck(ExpDataBase dataCheck) {
		this.dataCheck = dataCheck;
	}

	/**
	 * 设置导出的表名称
	 */
	public void setTableName(String tableName) {
		this.tableName = tableName;
	}

	/**
	 * 设置导出的文件名称
	 */
	public void setFileName(String fileName) {
		this.fileName = fileName;
	}

	/**
	 * 设置数据库链接.
	 */
	public void setConn(Connection conn) {
		this.conn = conn;
	}

	/**
	 * 文本切割符
	 */
	public void setSepertor(String sepertor) {
		this.sepertor = sepertor;
	}

	/**
	 * 生成.OK文件
	 */
	public void setOKfile(boolean isOKfile) {
		this.isOKfile = isOKfile;
	}

	/**
	 * 原有文件追加数据
	 */
	public void setAppend(boolean isAppend) {
		this.isAppend = isAppend;
	}

	/**
	 * 数据过滤条件
	 */
	public void setWhereStr(String whereStr) {
		this.whereStr = whereStr;
	}

	/**
	 * 文件编码
	 */
	public void setCharsetName(String charsetName) {
		this.charsetName = charsetName;
	}
}